SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE Procedure  [dbo].[SP_TCMOVI_ENRE_Q10]
/*-------------------------------------------------------*/
/*---Empresa              : OFISIS S.A.                --*/
/*---Cliente              : OFISIS S.A.		       --*/
/*---Sistema              : Tesoreria                  --*/
/*---M«dulo               : Empresa                    --*/
/*---Programa             : Cuentas Corrientes	       --*/
/*---Script               : tmdocu_p.sql               --*/
/*---Nombre SP            : SP_TCMOVI_ENRE_Q10	       --*/
/*---Desarrollado por     : Eduardo Vivar A.           --*/
/*---Fecha Creaci«n       : 15/06/2000                 --*/
/*---Base Datos           : Microsoft Sql Server       --*/
/*---Versi«n              : 7.0                        --*/
/*---Invoca a SP          :                            --*/
/*-------------------------------------------------------*/
/*-------------------------------------------------------*/
/*---Modificado 1 por     :			       --*/
/*---Fecha Modificaci«n   :			       --*/ 
/*---Detalle Modificaci«n :			       --*/ 
/*-------------------------------------------------------*/
/*-------------------------------------------------------*/
/*--- Drop Proc SP_TCMOVI_ENRE_Q10		       --*/
/*---Grant all on SP_TCMOVI_ENRE_Q10 to public         --*/ 
/*--- SP_TCMOVI_ENRE_Q10 '01', 'E', '00000003', 'SOL', '01/01/2000','12/12/2000','T'   --*/
/*--- SP_TCMOVI_ENRE_Q10 '01', 'E', '00000006', 'SOL', '01/01/2000','12/12/2000','T', NULL, NULL, NULL   --*/
/*-------------------------------------------------------*/

 @IDFE_INIC	 TD_DT_001,
 @IDFE_FINA	 TD_DT_001,
 @ISTI_REPO	 TD_VC_001,
 @ISCO_BANC      TD_VC_003,
 @ISNU_CNTA_BANC TD_VC_020,
 @ISCO_CAJA      TD_VC_008,
 @ISCA_WHER_LOCA VARCHAR(500) 
 as

 CREATE TABLE #TWCNTA_CRTE 
 ( CO_EMPR      VARCHAR (02) NULL,
   TI_ENTI	VARCHAR(1) NULL,  
   DE_TIPO_ENTI VARCHAR(50) NULL,
   CO_ENTI	VARCHAR(20) NULL, 
   DE_ENTI 	VARCHAR(80) NULL,
   CO_CABA_0001 VARCHAR(08) NULL,
   NU_CNTA_0001 VARCHAR(20) NULL,
   AA_0001	VARCHAR(04) NULL,
   MM_0001	VARCHAR(02) NULL,
   NU_COMP_0001 VARCHAR(08) NULL,
   TI_MOVI_0001	VARCHAR(1) NULL,
   FE_OPER_0001 DATETIME NULL,
   TI_OPER_0001	VARCHAR(03) NULL, 
   CO_MONE_0001 VARCHAR (03) NULL,
   IM_MOVI_0001 Numeric(16,4) NULL,

   TI_DOCU_0001 VARCHAR(03), 
   NU_DOCU_0001 VARCHAR(15), 
   CO_BCHE_0001 VARCHAR(03), 
   NU_CHEQ_0001 VARCHAR(15),

   NU_REND_GAST VARCHAR(10) NULL, 
   CO_CABA_0002	VARCHAR(08) NULL,
   NU_CNTA_0002 CHAR(20) NULL,
   AA_0002	VARCHAR(04) NULL,
   MM_0002	VARCHAR(02) NULL,
   NU_COMP_0002 VARCHAR(08) NULL,
   TI_MOVI_0002	VARCHAR(1) NULL,
   FE_OPER_0002 DATETIME NULL, 
   TI_OPER_0002	VARCHAR(03) NULL, 
   CO_MONE_0002	VARCHAR (03) NULL,
   IM_MOVI_0002 Numeric(16,4) NULL,

   TI_DOCU_0002 VARCHAR(03), 
   NU_DOCU_0002 VARCHAR(15), 
   CO_BCHE_0002 VARCHAR(03), 
   NU_CHEQ_0002 VARCHAR(15), 

   NU_AUXI Numeric(16,4) NULL,
   TI_ORIG VARCHAR(01) NULL,
   TI_DATO VARCHAR(01) NULL,
   DE_NOMB_BANC VARCHAR(100) NULL)

DECLARE  @VSCA_SELE       TD_VC_100,  
         @VSFE_INIC       TD_VC_010, 
	 @VSFE_FINA       TD_VC_010,
	 @VSCA_BANC_0001  TD_VC_100,
	 @VSCA_CAJA_0001  TD_VC_100,
	 @VSCA_BANC_0002  TD_VC_100,
	 @VSCA_CAJA_0002  TD_VC_100    
	 
	select  @ISCA_WHER_LOCA = REPLACE(@ISCA_WHER_LOCA ,'TMEMPR','T1')
	select  @ISCA_WHER_LOCA = REPLACE(@ISCA_WHER_LOCA ,'TMAUXI_EMPR.TI_AUXI_EMPR','T1.TI_ENTI')
	select  @ISCA_WHER_LOCA = REPLACE(@ISCA_WHER_LOCA ,'TMAUXI_EMPR.CO_AUXI_EMPR','T1.CO_ENTI')
    Select @VSCA_SELE = ''
    Select @VSCA_BANC_0001 = ''
    Select @VSCA_CAJA_0001 = ''
    Select @VSCA_BANC_0002 = ''
    Select @VSCA_CAJA_0002 = ''

	

  /*  If @ISCO_EMPR IS NOT NULL
       Select @VSCA_SELE = @VSCA_SELE + ' AND T1.CO_EMPR =  "'+@ISCO_EMPR+'"  '
    If @ISTI_AUXI_EMPR IS NOT NULL
       Select @VSCA_SELE = @VSCA_SELE + ' AND T1.TI_ENTI =  "' + @ISTI_AUXI_EMPR + '" '
If @ISCO_AUXI_EMPR IS NOT NULL
       Select @VSCA_SELE = @VSCA_SELE + ' AND T1.CO_ENTI =  RTRIM("' + @ISCO_AUXI_EMPR + '")  '
*/
    If @ISCO_BANC IS NOT NULL 
    BEGIN
       Select @VSCA_BANC_0001 = @VSCA_BANC_0001 + ' AND T1.CO_BANC =  RTRIM("' + @ISCO_BANC + '")  '    
       Select @VSCA_BANC_0002 = @VSCA_BANC_0002 + ' AND T5.CO_BANC =  RTRIM("' + @ISCO_BANC + '")  '    
     END
    If @ISNU_CNTA_BANC IS NOT NULL 
     BEGIN
       Select @VSCA_BANC_0001 = @VSCA_BANC_0001 + ' AND T1.NU_CNTA_BANC =  RTRIM("' + @ISNU_CNTA_BANC + '")  '    
       Select @VSCA_BANC_0002 = @VSCA_BANC_0002 + ' AND T5.NU_CNTA_BANC =  RTRIM("' + @ISNU_CNTA_BANC + '")  '    
     END
    If @ISCO_CAJA IS NOT NULL 
     BEGIN
       Select @VSCA_CAJA_0001 = @VSCA_CAJA_0001 + ' AND T1.CO_CAJA =  RTRIM("' + @ISCO_CAJA + '")  '    
       Select @VSCA_CAJA_0002 = @VSCA_CAJA_0002 + ' AND T3.CO_CAJA =  RTRIM("' + @ISCO_CAJA + '")  '    
     END

    Select @VSFE_INIC = Convert (varchar(10), @IDFE_INIC, 103)
    Select @VSFE_FINA = Convert (varchar(10), @IDFE_FINA, 103)

/*-----------------------   DATOS ---------------------------------*/      


IF @ISTI_REPO = 'T' OR @ISTI_REPO = 'B'
BEGIN
-- ENTREGAS A RENDIR (HACER JOIN CON TIPO DE OPERACION)
    EXECUTE (
	'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI, 
	CO_CABA_0001, NU_CNTA_0001, AA_0001, MM_0001, NU_COMP_0001, 
	TI_MOVI_0001, FE_OPER_0001, TI_OPER_0001, CO_MONE_0001, IM_MOVI_0001, 
	TI_DOCU_0001, NU_DOCU_0001, CO_BCHE_0001, NU_CHEQ_0001,
	NU_AUXI, TI_ORIG, TI_DATO, DE_NOMB_BANC) 

	SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_NOMB_ORDE, 
	T1.CO_BANC, T1.NU_CNTA_BANC, T1.AA_BNCO, T1.MM_BNCO, T1.NU_COMP_BANC, 
	T1.TI_MOVI_BANC, T1.FE_OPER, T1.CO_TIPO_OPER, T3.CO_MONE, 
	ROUND(
	( PATINDEX(T3.CO_MONE, T3.CO_MONE ) +
	PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, T3.CO_MONE )))*
	( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +
	  PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0) , 2), 
	T1.CO_TIPO_DOCU, T1.NU_DOCU_BANC, T1.CO_BANC_CHEQ, T1.NU_CHEQ,
	1, "B", "1", T5.DE_NOMB_BANC
	FROM TCMOVI_BANC T1, TTTIPO_OPER T2, TMCNTA_BANC T3, TMPARA_TESO T4, TTBANC_FINA T5
	WHERE T1.TI_MOVI_BANC = "E"
	'+ @VSCA_SELE + @VSCA_BANC_0001 + '
	AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)
	AND T1.TI_SITU != "ANU"
	AND T1.CO_EMPR = T2.CO_EMPR
	AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER
	AND T2.ST_ENRE_OTRO = "S"
	AND T1.CO_EMPR = T3.CO_EMPR
	AND T1.CO_BANC = T3.CO_BANC
	AND T1.NU_CNTA_BANC = T3.NU_CNTA_BANC
	AND T1.CO_EMPR = T4.CO_EMPR
	
	AND T1.CO_BANC = T5.CO_BANC
	
	AND T1.CO_BANC_ENRE IS NULL
	AND T1.NU_CNTA_ENRE IS NULL
	AND T1.AA_ENRE IS NULL        
	AND T1.MM_ENRE IS NULL 
	AND T1.NU_COMP_ENRE IS NULL
	AND T1.NU_REND_GAST IS NULL') 

-- EGRESOS/INGRESOS -> REEMBOLSOS/DEVOLUCIONES (HACER JOIN CON TIPO DE OPERACION) --

 -- POR BANCOS 
 EXECUTE (
	'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI, 
	CO_CABA_0001, NU_CNTA_0001, AA_0001, MM_0001, NU_COMP_0001, 
	TI_MOVI_0001, FE_OPER_0001, 
	NU_REND_GAST, 
	CO_CABA_0002, NU_CNTA_0002, AA_0002, MM_0002, NU_COMP_0002, 
	TI_MOVI_0002, FE_OPER_0002, TI_OPER_0002, CO_MONE_0002,
	IM_MOVI_0002, 
	TI_DOCU_0002, NU_DOCU_0002, CO_BCHE_0002, NU_CHEQ_0002,
	NU_AUXI, TI_ORIG, TI_DATO ) 
	SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_NOMB_ORDE, 
	T1.CO_BANC_ENRE, T1.NU_CNTA_ENRE, T1.AA_ENRE, T1.MM_ENRE, T1.NU_COMP_ENRE, 
	"E", T5.FE_OPER, 
	T1.NU_REND_GAST, 	
	T1.CO_BANC, T1.NU_CNTA_BANC, T1.AA_BNCO, T1.MM_BNCO, T1.NU_COMP_BANC,
	T1.TI_MOVI_BANC, T1.FE_OPER, T1.CO_TIPO_OPER, T6.CO_MONE, 
	ROUND(
	( PATINDEX(T3.CO_MONE, T6.CO_MONE ) +
	PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, T6.CO_MONE )))*
	( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +
	  PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0), 2), 
	T5.CO_TIPO_DOCU, T5.NU_DOCU_BANC, T5.CO_BANC_CHEQ, T5.NU_CHEQ,
	( PATINDEX(T1.TI_MOVI_BANC,"E")- PATINDEX(T1.TI_MOVI_BANC,"I")) , "B", "2"
	FROM TCMOVI_BANC T1, TTTIPO_OPER T2, TMCNTA_BANC T3, TMPARA_TESO T4, TCMOVI_BANC T5, TMCNTA_BANC T6
	WHERE T1.CO_EMPR = T2.CO_EMPR
	'+ @VSCA_SELE + @VSCA_BANC_0002 + '
	AND T5.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)
	AND T1.TI_SITU != "ANU"
	AND T5.TI_SITU != "ANU"
	AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER
	AND T2.ST_ENRE_OTRO = "S"
	AND T1.CO_EMPR = T3.CO_EMPR
	AND T1.CO_BANC = T3.CO_BANC
	AND T1.NU_CNTA_BANC = T3.NU_CNTA_BANC
	AND T1.CO_EMPR = T4.CO_EMPR
	AND T1.CO_BANC_ENRE IS NOT NULL
	AND T1.NU_CNTA_ENRE IS NOT NULL
	AND T1.AA_ENRE IS NOT NULL        
	AND T1.MM_ENRE IS NOT NULL 
	AND T1.NU_COMP_ENRE IS NOT NULL
	AND T1.NU_REND_GAST IS NOT NULL	
	AND T1.CO_EMPR = T5.CO_EMPR
	AND T1.CO_BANC_ENRE = T5.CO_BANC
	AND T1.NU_CNTA_ENRE = T5.NU_CNTA_BANC
	AND T1.AA_ENRE = T5.AA_BNCO
	AND T1.MM_ENRE = T5.MM_BNCO
	AND T1.NU_COMP_ENRE = T5.NU_COMP_BANC
	AND T5.TI_MOVI_BANC = "E"  
	AND T5.CO_EMPR = T6.CO_EMPR
	AND T5.CO_BANC = T6.CO_BANC
	AND T5.NU_CNTA_BANC = T6.NU_CNTA_BANC
') 

 -- POR CAJA
    EXECUTE (
	'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI, 
	CO_CABA_0001, NU_CNTA_0001, AA_0001, MM_0001, NU_COMP_0001, 
	TI_MOVI_0001, FE_OPER_0001, NU_REND_GAST, 
	CO_CABA_0002, AA_0002, MM_0002, NU_COMP_0002, 
	TI_MOVI_0002, FE_OPER_0002, TI_OPER_0002, CO_MONE_0002,
	IM_MOVI_0002, 
	NU_AUXI, TI_ORIG, TI_DATO ) 
	SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_ENTI, 
	T1.CO_BANC_ENRE, T1.NU_CNTA_ENRE, T1.AA_ENRE, T1.MM_ENRE, T1.NU_COMP_ENRE, 
	"E", T5.FE_OPER, T1.NU_REND_GAST,
	T1.CO_CAJA, T1.AA_CAJA, T1.MM_CAJA, T1.NU_COMP_CAJA,
	T1.TI_MOVI, T1.FE_OPER, T1.CO_TIPO_OPER, T3.CO_MONE, 
	ROUND(
	( PATINDEX(T3.CO_MONE, T3.CO_MONE ) +
	PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, T3.CO_MONE )))*
	( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +
	  PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0) , 2), 
	( PATINDEX(T1.TI_MOVI,"E")- PATINDEX(T1.TI_MOVI,"I")) , "C", "2"
	FROM TCMOVI_CAJA T1, TTTIPO_OPER T2, TMCAJA T3, TMPARA_TESO T4, TCMOVI_BANC T5
	WHERE T1.CO_EMPR = T2.CO_EMPR
	'+ @VSCA_SELE +@VSCA_CAJA_0002 + '
	AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)
	AND T1.TI_SITU != "ANU"
	AND T5.TI_SITU != "ANU"
	AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER
	AND T2.ST_ENRE_OTRO = "S"
	AND T1.CO_EMPR = T3.CO_EMPR
	AND T1.CO_BANC_ENRE IS NOT NULL
	AND T1.NU_CNTA_ENRE IS NOT NULL
	AND T1.CO_EMPR = T4.CO_EMPR
	AND T1.CO_CAJA_ENRE IS NOT NULL
	AND T1.AA_ENRE IS NOT NULL        
	AND T1.MM_ENRE IS NOT NULL 
	AND T1.NU_COMP_ENRE IS NOT NULL
	AND T1.NU_REND_GAST IS NOT NULL
	
	AND T1.CO_EMPR = T5.CO_EMPR
	AND T1.CO_BANC_ENRE = T5.CO_BANC
	AND T1.NU_CNTA_ENRE = T5.NU_CNTA_BANC
	AND T1.AA_ENRE = T5.AA_BNCO
	AND T1.MM_ENRE = T5.MM_BNCO
	AND T1.NU_COMP_ENRE = T5.NU_COMP_BANC
	AND T5.TI_MOVI_BANC = "E" ') 

--RENDICIONES
 EXECUTE (
	'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI, 
	CO_CABA_0001, NU_CNTA_0001, AA_0001, MM_0001, NU_COMP_0001, 
	TI_MOVI_0001, FE_OPER_0001, NU_REND_GAST, 
	FE_OPER_0002, IM_MOVI_0002, 
	TI_DOCU_0002, NU_DOCU_0002, CO_BCHE_0002, NU_CHEQ_0002,
	NU_AUXI, TI_ORIG, TI_DATO) 
	SELECT MAX(T2.CO_EMPR), T1.TI_ENTI, T1.CO_ENTI, MAX(T1.DE_NOMB_ORDE), 
	MAX(T2.CO_BANC_DEST), MAX(T2.NU_CNTA_DEST), MAX(T2.AA_OPER_DEST), MAX(T2.MM_OPER_DEST),  MAX(T2.NU_COMP_DEST), 
	MAX(T1.TI_MOVI_BANC), MAX(T1.FE_OPER), T2.NU_REND_GAST, 
	MAX(T2.FE_REND_GAST),  
	ROUND(
	SUM(( PATINDEX(T4.CO_MONE, T4.CO_MONE ) +
	PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T4.CO_MONE, T4.CO_MONE )))*
	( PATINDEX(T4.CO_MONE, T5.CO_MONE_DEFA)/T3.FA_CAMB_DOCU  +
	  PATINDEX(T4.CO_MONE, T5.CO_MONE_EXTR)*T3.FA_CAMB_DOCU ))*ISNULL(T3.IM_CONV,0)), 2), 
	MAX(T1.CO_TIPO_DOCU), MAX(T1.NU_DOCU_BANC), MAX(T1.CO_BANC_CHEQ), MAX(T1.NU_CHEQ),
	-1, "R", "2"
	FROM TCREND_GAST T2, TCMOVI_BANC T1, TDREND_GAST T3, TMCNTA_BANC T4, TMPARA_TESO T5--, TREMPR_GRUP T8, TMUSUA T9 
	WHERE T1.TI_MOVI_BANC = "E" 
	'+ @VSCA_SELE + @VSCA_BANC_0001 + '
	AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)
	AND T1.TI_SITU != "ANU"
	AND T2.TI_SITU != "ANU"
	AND T2.CO_EMPR = T1.CO_EMPR
	AND T2.AA_OPER_DEST = T1.AA_BNCO
	AND T2.MM_OPER_DEST = T1.MM_BNCO
	AND T2.CO_BANC_DEST = T1.CO_BANC
	AND T2.NU_CNTA_DEST = T1.NU_CNTA_BANC
	AND T2.NU_COMP_DEST = T1.NU_COMP_BANC
	'+ @ISCA_WHER_LOCA + '
	AND T2.CO_EMPR = T3.CO_EMPR
	AND T2.NU_REND_GAST = T3.NU_REND_GAST
	AND T2.FE_REND_GAST = T3.FE_REND_GAST
	AND T1.CO_EMPR = T4.CO_EMPR
	AND T1.CO_BANC = T4.CO_BANC
	AND T1.NU_CNTA_BANC = T4.NU_CNTA_BANC
	AND T1.CO_EMPR = T5.CO_EMPR
	GROUP BY T2.NU_REND_GAST, T1.TI_ENTI, T1.CO_ENTI')
END

IF @ISTI_REPO = 'T' OR @ISTI_REPO = 'C'
BEGIN
-- ENTREGAS A RENDIR (HACER JOIN CON TIPO DE OPERACION)
    EXECUTE (
	'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI, 
	CO_CABA_0001, AA_0001, MM_0001, NU_COMP_0001, 
	TI_MOVI_0001, FE_OPER_0001, TI_OPER_0001, CO_MONE_0001, IM_MOVI_0001, NU_AUXI, 
	TI_ORIG, TI_DATO, DE_NOMB_BANC) 
	SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_ENTI, 
	T1.CO_CAJA, T1.AA_CAJA, T1.MM_CAJA, T1.NU_COMP_CAJA, 
	T1.TI_MOVI, T1.FE_OPER, T1.CO_TIPO_OPER, T3.CO_MONE, 
	ROUND(
	( PATINDEX(T3.CO_MONE, T3.CO_MONE ) +
	PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, T3.CO_MONE )))*
	( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +
	  PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0), 2), 
	1, "C", "1", T3.DE_CAJA
	FROM TCMOVI_CAJA T1, TTTIPO_OPER T2, TMCAJA T3, TMPARA_TESO T4
	WHERE T1.TI_MOVI = "E"
	'+ @VSCA_SELE + @VSCA_CAJA_0001 + '
	AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)
	AND T1.TI_SITU != "ANU"
	AND T1.CO_EMPR = T2.CO_EMPR
	AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER
	AND T2.ST_ENRE_OTRO = "S"
	AND T1.CO_EMPR = T3.CO_EMPR
	AND T1.CO_CAJA = T3.CO_CAJA
	AND T1.CO_EMPR = T4.CO_EMPR
	AND T1.CO_CAJA_ENRE IS NULL
	AND T1.AA_ENRE IS NULL        

	AND T1.MM_ENRE IS NULL 
	AND T1.NU_COMP_ENRE IS NULL
	AND T1.NU_REND_GAST IS NULL') 

-- EGRESOS/INGRESOS -> REEMBOLSOS/DEVOLUCIONES (HACER JOIN CON TIPO DE OPERACION) aqui

 --POR BANCOS
 EXECUTE (
	'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI, 
	CO_CABA_0001, NU_CNTA_0001, AA_0001, MM_0001, NU_COMP_0001, 
	TI_MOVI_0001, FE_OPER_0001, NU_REND_GAST, 
	CO_CABA_0002, AA_0002, MM_0002, NU_COMP_0002, 
	TI_MOVI_0002, FE_OPER_0002, TI_OPER_0002, CO_MONE_0002,
	IM_MOVI_0002, 
	NU_AUXI, TI_ORIG, TI_DATO ) 
	SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_ENTI, 
	T1.CO_BANC_ENRE, T1.NU_CNTA_ENRE, T1.AA_ENRE, T1.MM_ENRE, T1.NU_COMP_ENRE, 
	"E", T5.FE_OPER, T1.NU_REND_GAST,
	T1.CO_CAJA, T1.AA_CAJA, T1.MM_CAJA, T1.NU_COMP_CAJA,
	T1.TI_MOVI, T1.FE_OPER, T1.CO_TIPO_OPER, T6.CO_MONE, 
	ROUND(
	( PATINDEX(T3.CO_MONE, T6.CO_MONE ) +
	PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, T6.CO_MONE )))*
	( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +
	  PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0), 2), 
	( PATINDEX(T1.TI_MOVI,"E")- PATINDEX(T1.TI_MOVI,"I")) , "C", "2"
	FROM TCMOVI_CAJA T1, TTTIPO_OPER T2, TMCAJA T3, TMPARA_TESO T4, TCMOVI_BANC T5, TMCNTA_BANC T6
	WHERE T1.CO_EMPR = T2.CO_EMPR
	'+ @VSCA_SELE +@VSCA_CAJA_0002 + '
	AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)
	AND T1.TI_SITU != "ANU"
	AND T5.TI_SITU != "ANU"
	AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER
	AND T2.ST_ENRE_OTRO = "S"
	AND T1.CO_EMPR = T3.CO_EMPR
	AND T1.CO_CAJA = T3.CO_CAJA
	AND T1.CO_EMPR = T4.CO_EMPR
	AND T1.CO_BANC_ENRE IS NOT NULL
	AND T1.NU_CNTA_ENRE IS NOT NULL
	AND T1.AA_ENRE IS NOT NULL        
	AND T1.MM_ENRE IS NOT NULL 
	AND T1.NU_COMP_ENRE IS NOT NULL
	AND T1.NU_REND_GAST IS NOT NULL	
	AND T1.CO_EMPR = T5.CO_EMPR
	AND T1.CO_BANC_ENRE = T5.CO_BANC
	AND T1.NU_CNTA_ENRE = T5.NU_CNTA_BANC
	AND T1.AA_ENRE = T5.AA_BNCO
	AND T1.MM_ENRE = T5.MM_BNCO
	AND T1.NU_COMP_ENRE = T5.NU_COMP_BANC
	AND T5.TI_MOVI_BANC = "E"
	AND T5.CO_EMPR = T6.CO_EMPR
	AND T5.CO_BANC = T6.CO_BANC
	AND T5.NU_CNTA_BANC = T6.NU_CNTA_BANC ')

 -- POR CAJA
 EXECUTE (
	'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI, 
	CO_CABA_0001, AA_0001, MM_0001, NU_COMP_0001, 
	TI_MOVI_0001, FE_OPER_0001, NU_REND_GAST, 
	CO_CABA_0002, AA_0002, MM_0002, NU_COMP_0002, 
	TI_MOVI_0002, FE_OPER_0002, TI_OPER_0002, CO_MONE_0002,
	IM_MOVI_0002, NU_AUXI, TI_ORIG, TI_DATO ) 
	SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_ENTI, 
	T1.CO_CAJA_ENRE, T1.AA_ENRE, T1.MM_ENRE, T1.NU_COMP_ENRE, 
	"E", T5.FE_OPER, T1.NU_REND_GAST,
	T1.CO_CAJA, T1.AA_CAJA, T1.MM_CAJA, T1.NU_COMP_CAJA,
	T1.TI_MOVI, T1.FE_OPER, T1.CO_TIPO_OPER, T6.CO_MONE, 
	ROUND(
	( PATINDEX(T3.CO_MONE, T6.CO_MONE ) +
	PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, T6.CO_MONE )))*
	( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +
	  PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0), 2), 
	( PATINDEX(T1.TI_MOVI,"E")- PATINDEX(T1.TI_MOVI,"I")) , "C", "2"
	FROM TCMOVI_CAJA T1, TTTIPO_OPER T2, TMCAJA T3, TMPARA_TESO T4, TCMOVI_CAJA T5, TMCAJA T6
	WHERE T1.CO_EMPR = T2.CO_EMPR
	'+ @VSCA_SELE +@VSCA_CAJA_0002 + '
	AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)
	AND T1.TI_SITU != "ANU"
	AND T5.TI_SITU != "ANU"
	AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER
	AND T2.ST_ENRE_OTRO = "S"
	AND T1.CO_EMPR = T3.CO_EMPR
	AND T1.CO_CAJA = T3.CO_CAJA
	AND T1.CO_EMPR = T4.CO_EMPR
	AND T1.CO_CAJA_ENRE IS NOT NULL
	AND T1.AA_ENRE IS NOT NULL        
	AND T1.MM_ENRE IS NOT NULL 
	AND T1.NU_COMP_ENRE IS NOT NULL
	AND T1.NU_REND_GAST IS NOT NULL
	
	AND T1.CO_EMPR = T5.CO_EMPR
	AND T1.CO_CAJA_ENRE = T5.CO_CAJA
	AND T1.AA_ENRE = T5.AA_CAJA
	AND T1.MM_ENRE = T5.MM_CAJA
	AND T1.NU_COMP_ENRE = T5.NU_COMP_CAJA
	AND T5.TI_MOVI = "E" 
	AND T1.CO_EMPR = T6.CO_EMPR
	AND T1.CO_CAJA_ENRE = T6.CO_CAJA') 

--RENDICIONES
 EXECUTE (
	'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI, 
	CO_CABA_0001, AA_0001, MM_0001, NU_COMP_0001, 
	TI_MOVI_0001, FE_OPER_0001, NU_REND_GAST, 
	FE_OPER_0002, IM_MOVI_0002, NU_AUXI, TI_ORIG, TI_DATO) 
	SELECT MAX(T2.CO_EMPR), T1.TI_ENTI, T1.CO_ENTI, MAX(T1.DE_ENTI), 
	MAX(T2.CO_CAJA_DEST), MAX(T2.AA_OPER_DEST), MAX(T2.MM_OPER_DEST),  MAX(T2.NU_COMP_DEST), 
	MAX(T1.TI_MOVI), MAX(T1.FE_OPER), T2.NU_REND_GAST, 
	MAX(T2.FE_REND_GAST), 
	ROUND(
	SUM(( PATINDEX(T4.CO_MONE, T4.CO_MONE ) +
	PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T4.CO_MONE, T4.CO_MONE )))*
	( PATINDEX(T4.CO_MONE, T5.CO_MONE_DEFA)/T3.FA_CAMB_DOCU  +
	  PATINDEX(T4.CO_MONE, T5.CO_MONE_EXTR)*T3.FA_CAMB_DOCU ))*ISNULL(T3.IM_CONV,0)), 2), 
	-1, "R", "2"
	FROM TCREND_GAST T2, TCMOVI_CAJA T1, TDREND_GAST T3, TMCAJA T4, TMPARA_TESO T5--, TREMPR_GRUP T8, TMUSUA T9 
	
	WHERE T1.TI_MOVI = "E" 
	'+ @VSCA_SELE + @VSCA_CAJA_0001 + '
	AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)
	AND T1.TI_SITU != "ANU"
	AND T2.TI_SITU != "ANU"
	AND T2.CO_EMPR = T1.CO_EMPR
	AND T2.AA_OPER_DEST = T1.AA_CAJA
	AND T2.MM_OPER_DEST = T1.MM_CAJA
	AND T2.CO_CAJA_DEST = T1.CO_CAJA
	'+ @ISCA_WHER_LOCA + '
	AND T2.NU_COMP_DEST = T1.NU_COMP_CAJA
	AND T2.CO_EMPR = T3.CO_EMPR
	AND T2.NU_REND_GAST = T3.NU_REND_GAST
	AND T2.FE_REND_GAST = T3.FE_REND_GAST
	AND T1.CO_EMPR = T4.CO_EMPR
	AND T1.CO_CAJA = T4.CO_CAJA
	AND T1.CO_EMPR = T5.CO_EMPR
	GROUP BY T2.NU_REND_GAST, T1.TI_ENTI, T1.CO_ENTI')
END



SELECT T1.CO_EMPR, MAX(T2.DE_NOMB), T1.TI_ENTI, MAX(T3.NO_TIPO_AUXI), T1.CO_ENTI, MAX(T1.DE_ENTI), 
NULL, NULL, 
NULL, 
NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, 
NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL,
NULL, 
NULL, NULL, NULL, NULL,
NULL, NULL,  NULL, ROUND(ISNULL(SUM(ISNULL(T1.IM_MOVI_0001,0)*ISNULL(T1.NU_AUXI,0)),0)
+ ISNULL(SUM(ISNULL(T1.IM_MOVI_0002,0)*ISNULL(T1.NU_AUXI,0)),0),2),
NULL
FROM #TWCNTA_CRTE T1, TMEMPR T2, TTAUXI_EMPR T3 
WHERE T1.FE_OPER_0001 < @IDFE_INIC
AND T1.CO_EMPR = T2.CO_EMPR
AND T1.CO_EMPR = T3.CO_EMPR
AND RTRIM(T1.TI_ENTI) = RTRIM(T3.TI_AUXI_EMPR)
GROUP BY T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI

UNION ALL 

SELECT T1.CO_EMPR, T2.DE_NOMB, T1.TI_ENTI, T3.NO_TIPO_AUXI, T1.CO_ENTI, T1.DE_ENTI, 
T1.CO_CABA_0001, T1.NU_CNTA_0001, 
T1.AA_0001+T1.MM_0001+T1.NU_COMP_0001+T1.TI_MOVI_0001, 
T1.AA_0001, T1.MM_0001, T1.NU_COMP_0001, T1.TI_MOVI_0001, 
T1.FE_OPER_0001, T1.TI_OPER_0001, T1.CO_MONE_0001, T1.IM_MOVI_0001,
T1.TI_DOCU_0001, T1.NU_DOCU_0001, T1.CO_BCHE_0001, T1.NU_CHEQ_0001,
T1.NU_REND_GAST, 
T1.CO_CABA_0002, T1.NU_CNTA_0002, T1.AA_0002, T1.MM_0002, T1.NU_COMP_0002, 
T1.TI_MOVI_0002, T1.FE_OPER_0002, T1.TI_OPER_0002, T1.CO_MONE_0002,
T1.IM_MOVI_0002, 
T1.TI_DOCU_0002, T1.NU_DOCU_0002, T1.CO_BCHE_0002, T1.NU_CHEQ_0002,
T1.NU_AUXI, T1.TI_ORIG,  TI_DATO, NULL,
T1.DE_NOMB_BANC
FROM #TWCNTA_CRTE T1, TMEMPR T2, TTAUXI_EMPR T3 
WHERE T1.FE_OPER_0001 >= @IDFE_INIC
AND T1.CO_EMPR = T2.CO_EMPR
AND T1.CO_EMPR = T3.CO_EMPR
AND T1.TI_ENTI = T3.TI_AUXI_EMPR


ORDER BY 1, 3, 5, 7, 8, 9, 39, 14, 29, 38 DESC

RETURN

/********************************* FIN ***********************************/


GO
